package org.obis.portal.ws.action;

import java.util.LinkedList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.obis.portal.ws.model.SpeciesDistributionRecord;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.opensymphony.xwork2.Action;

/**
 * This unpacks the request parameters, build the corresponding SQL,
 * execute it, build an object with the results, ready for the template to 
 * render the response
 * @author tim
 */
public class SpeciesDistributionRecordAction extends JdbcDaoSupport implements Action {
	/**
	 * Generated by eclipse 
	 */
	private static final long serialVersionUID = 1087797270292047239L;
	protected final Log log = LogFactory.getLog(getClass());
	
	/**
	 * The following are the request parameters
	 * NOTE:
	 *   At the time of writing there is a limitation that if the user passes 2 parameters of the same 
	 *   name, only 1 will be used.
	 *   e.g. scientificName=A&scientificName=B will NOT yet do a "scientificName=A or scientificName=B"
	 */
	protected String scientificName;
	protected String maxLatitude;
	protected String minLatitude;
	protected String maxLongitude;
	protected String minLongitude;
	
	/**
	 * The container List for a LIST search
	 */
	protected List<SpeciesDistributionRecord> sdrs;
	
	/**
	 * The result of a GET operation 
	 */
	protected List<SpeciesDistributionRecord> sdr;
	
	/**
	 * Flag to indicate if more records are available 
	 */
	protected boolean hasMore = false;
	
	/**
	 * Flag to indicate whether classification information has to be used to include all children of taxon with scientificName 
	 * Default is false: returned set restricted to records identified to level of scientificName 
	 */
	protected boolean includeChildren = false;
		
	/**
	 * Flag to indicate whether synonym information has to be used to include all taxa pointing at taxon with scientificName as valid 
	 * Default is false: returned set restricted to records identified to exact scientificName 
	 */
	protected boolean includeSynonyms = false;
		
	/**
	 * Indicating format sent to the requester; default csv, can be xml 
	 */
	protected String returnFormat = "csv";
		
	/**
	 * Number of records to be skipped before returning records
	 * to allow paging through all available records 
	 */
	protected int skip  = 0;
		
	/**
	 * Factory to convert a single row from the SQL result to an object 
	 */
	protected SpeciesDistributionRecordRowMapper sdrRowMapper = new SpeciesDistributionRecordRowMapper();
	
	/**
	 * 1000 is a good trade off to minimise paging and stop huge result sizes
	 */
	protected static final int RESULTS_PER_PAGE = 1000;
	
	/**
	 * ID of the OBIS distribution record to be pulled by get 
	 */
	protected long id;
	
	/**
	 * The parameters are automatically pulled from the request by Struts2 for us, so this
	 * maps those to a SQL query, executes it against the DB and builds the result list 
	 *  
	 * @return SUCCESS or ERROR depending on what happened with the DB
	 */
	public String help() {
		log.debug("looking for help");
		return "success";
	}
	
	@SuppressWarnings("unchecked")
	public String get() {
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT drs.id, tu_displayname as scientificname, latitude, longitude, resfullname ");
		sql.append("from obis.drs inner join obis.resources on drs.resource_id=resources.id ");
		sql.append("inner join obis.taxa on taxon_id=taxa.id ");
		sql.append("inner join obis.cleannames on cleanname_id=cleannames.id ");
		sql.append("inner join obis.tu on cleannames.worms_id=tu.id ");
		sql.append("WHERE drs.id=?");
		List<Object> sqlParams = new LinkedList<Object>();
		sqlParams.add(id);
		sdrs = (List<SpeciesDistributionRecord>) getJdbcTemplate()
		.query(sql.toString(),
				// add the SQL parameters
				sqlParams.toArray(),
				new RowMapperResultSetExtractor(sdrRowMapper, RESULTS_PER_PAGE+1));
		log.debug(sql + " - [" + sqlParams + "]");
		log.debug("Format used " + returnFormat);
		return returnFormat;
	}
	
	@SuppressWarnings("unchecked")
	public String list() {

		StringBuffer sql = new StringBuffer();
		// NOTE that the sdrRowMapper uses the names here to build the object - proceed with caution
		sql.append("SELECT drs.id, tu_displayname as scientificname, latitude, longitude, resfullname ");
		sql.append("from obis.drs inner join obis.resources on drs.resource_id=resources.id ");
		sql.append("inner join obis.taxa on drs.taxon_id=taxa.id ");
		sql.append("inner join obis.cleannames on cleannames.id=taxa.cleanname_id ");
		if (includeSynonyms){
			log.debug("include synonyms");
			sql.append("inner join obis.cleannames v on cleannames.valid_id=v.id ");
			sql.append("inner join obis.tu on tu.id=v.worms_id WHERE ");
		}
		else {
			log.debug("don't include synonyms");
			sql.append("inner join obis.tu on tu.id=cleannames.worms_id WHERE ");
		}
		List<String> where = new LinkedList<String>();
		List<Object> sqlParams = new LinkedList<Object>();

		if (scientificName!=null) {
			if (includeChildren) {
				log.debug("include children");
				where.add("tu_sp~('^'||(select tu_sp from obis.tu where tu_displayname=?)" +
					"||(select id from obis.tu where tu_displayname=?)||'#') " +
					"or tu_displayname=?");
				sqlParams.add(scientificName);
				sqlParams.add(scientificName);
			}
			else {
				log.debug("don't include children");
				where.add("scientificname=?");
			}
			sqlParams.add(scientificName);
		}
		if (maxLatitude!=null) {
			where.add("latitude<=?");
			sqlParams.add(maxLatitude);
		}
		
		if (minLatitude!=null) {
			where.add("latitude>=?");
			sqlParams.add(minLatitude);
		}
		
		if (maxLongitude!=null) {
			where.add("longitude<=?");
			sqlParams.add(maxLongitude);
		}
		
		if (minLongitude!=null) {
			where.add("longitude>=?");
			sqlParams.add(minLongitude);
		}
		
		for (int i=0; i<where.size(); i++) {
			sql.append(where.get(i));
			if (i<where.size()-1) {
				sql.append(" AND ");
			}
		}
		
		sql.append(" ORDER BY id LIMIT ? OFFSET ?");
		sqlParams.add(RESULTS_PER_PAGE+1);
		sqlParams.add(skip);
		
		log.debug(sql + " - [" + sqlParams + "]");
		
		// we build the SQL with ? and then use the parameters as Java will automatically do the 
		// type conversion for us (e.g. dates and floats etc)
		sdrs = (List<SpeciesDistributionRecord>) getJdbcTemplate()
			.query(sql.toString(),
					// add the SQL parameters
					sqlParams.toArray(),
					new RowMapperResultSetExtractor(sdrRowMapper, RESULTS_PER_PAGE+1));
		
		if (sdrs.size() > RESULTS_PER_PAGE) {
			hasMore = true;
		}
		
		log.debug("Records returned[" + sdrs.size() + "]");
		//returnFormat=returnFormat();
		log.debug("Format used " + returnFormat);
		
		return returnFormat;
	}

	/**
	 * Required method, not used 
	 */
	@Override
	public String execute() throws Exception {
		return SUCCESS;
	}
	
	// ONLY GETTERS AND SETTERS FOLLOW
	public String getScientificName() {
		return scientificName;
	}
	public void setScientificName(String scientificName) {
		this.scientificName = scientificName;
	}
	public String getMaxLatitude() {
		return maxLatitude;
	}
	public void setMaxLatitude(String maxLatitude) {
		this.maxLatitude = maxLatitude;
	}
	public String getMinLatitude() {
		return minLatitude;
	}
	public void setMinLatitude(String minLatitude) {
		this.minLatitude = minLatitude;
	}
	public String getMaxLongitude() {
		return maxLongitude;
	}
	public void setMaxLongitude(String maxLongitude) {
		this.maxLongitude = maxLongitude;
	}
	public String getMinLongitude() {
		return minLongitude;
	}
	public void setMinLongitude(String minLongitude) {
		this.minLongitude = minLongitude;
	}
	public List<SpeciesDistributionRecord> getSdrs() {
		return sdrs;
	}
	public void setSdrs(List<SpeciesDistributionRecord> sdrs) {
		this.sdrs = sdrs;
	}
	public List<SpeciesDistributionRecord> getSdr() {
		return sdr;
	}
	public void setSdr(List<SpeciesDistributionRecord> sdr) {
		this.sdr = sdr;
	}
	public boolean isHasMore() {
		return hasMore;
	}
	public void setHasMore(boolean hasMore) {
		this.hasMore = hasMore;
	}
	public boolean includeChildren() {
		return includeChildren;
	}
	public void setIncludeChildren(boolean includeChildren) {
		this.includeChildren = includeChildren;
	}
	public boolean includeSynonyms() {
		return includeSynonyms;
	}
	public void setIncludeSynonyms(boolean includeSynonyms) {
		this.includeSynonyms = includeSynonyms;
	}
	public String returnFormat() {
		return returnFormat;
	}
	public void setReturnFormat(String returnFormat) {
		this.returnFormat = returnFormat;
	}

	public int getSkip() {
		return skip;
	}

	public void setSkip(int skip) {
		this.skip = skip;
	}

	public boolean isIncludeChildren() {
		return includeChildren;
	}

	public boolean isIncludeSynonyms() {
		return includeSynonyms;
	}

	public String getReturnFormat() {
		return returnFormat;
	}

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}
}
